﻿using dpa.Library.Helper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace dpa.Library.Services;

public class ExcelService : IExcelService
{
    public HSSFWorkbook LoadExcel(string excelFilePath)
        => new HSSFWorkbook(new FileStream(excelFilePath, FileMode.Open));
    
    public Task<string> GetValueByRowNoAndCellNo(HSSFWorkbook workbook, int rowNo, string cellNo)
    {
        ISheet sheet = workbook.GetSheetAt(0);
        ICell cell = sheet.GetRow(rowNo).GetCell(ExcelHelper.ConvertToNumber(cellNo));
        string cellValue = cell.ToString();
        return Task.FromResult(cellValue);
    }
    
    public Task<string> GetValueByRowNoAndCellNo(HSSFWorkbook workbook, int rowNo, int cellNo)
    {
        ISheet sheet = workbook.GetSheetAt(0);
        ICell cell = sheet.GetRow(rowNo).GetCell(cellNo);
        string cellValue = cell.ToString();
        return Task.FromResult(cellValue);
    }

    public async Task<(int rowNo, int cellNo)?> FindKeyWordInColum(HSSFWorkbook workbook, int columnIndex,
        string keyWord)
    {
        // 获取指定工作表
        ISheet sheet = workbook.GetSheetAt(0);
            
        // 遍历行(注意:GetRow可能返回null)
        for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
        {
            IRow row = sheet.GetRow(rowIndex);

            // 获取指定列的单元格
            ICell cell = row?.GetCell(columnIndex);

            // 获取单元格文本值并比较
            string cellValue = cell?.ToString();
            if (cellValue != null && cellValue.IsMatchText(@$"^{keyWord}"))
            {
                return (rowIndex, columnIndex);
            }
        }
        return null;
    }
    
}